﻿-- =============================================
-- Author:		<YJT>
-- Create date: <2012-09>
-- Description:	<根据UserName得到需要提醒的记录>
-- =============================================
CREATE PROCEDURE [dbo].[proc_TimeRemind_RemindList]
(
	@UserName VARCHAR(50)
)

AS
SET DATEFIRST 1 --设定每周的第一天为星期一
BEGIN

SELECT * FROM TimeRemind -- 一次提醒
WHERE UserName=@UserName AND TimeType=1
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-10,GETDATE())) --上次提醒为空或是在10分钟以前提醒过，用来控制时间提醒的间隔
AND RemindTime>=DATEADD(Minute,-10,GETDATE()) --过期提醒 提醒范围
AND RemindTime<=DATEADD(Minute,30,GETDATE()) --提前30分钟提醒

UNION 

SELECT * FROM TimeRemind -- 每天提醒
WHERE UserName=@UserName AND TimeType=2 
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-30,GETDATE())) -- 上次提醒时间为空或在30分钟以前
AND RemindDay<>'' -- 每天提醒的时间不为空
-- RemindDay和当前日期组合成每日需要提醒的时间
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)>=DATEADD(Minute,-10,GETDATE()) 
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)<=DATEADD(Minute,30,GETDATE())

UNION 

SELECT * FROM TimeRemind -- 每周提醒
WHERE UserName=@UserName AND TimeType=3 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-30,GETDATE()))
AND charindex(','+ltrim(DATEPART(weekday,GETDATE()))+',',','+RemindWeekday+',')>0 -- 判断当前星期是否在RemindWeekday中
AND RemindDay<>'' 
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)>=DATEADD(Minute,-10,GETDATE())
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)<=DATEADD(Minute,30,GETDATE())

UNION

SELECT * FROM TimeRemind -- 每月提醒
WHERE UserName=@UserName AND TimeType=4 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时，就可以实现每天只提醒一次
AND ( charindex(','+ltrim(DAY(GETDATE()))+',',','+RemindMonth+',')>0 --判断当前日期是否需要提醒
	OR charindex(','+ltrim(DAY(GETDATE())+1)+',',','+RemindMonth+',')>0  --提前一天
	OR charindex(','+ltrim(DAY(GETDATE())+2)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+3)+',',','+RemindMonth+',')>0
)

UNION

SELECT * FROM TimeRemind --每年提醒
WHERE UserName=@UserName AND TimeType=5 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)>=DATEADD(DAY,-1,GETDATE()) -- 时间范围 -1当天
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)<=DATEADD(DAY,3,GETDATE()) -- 

------------------------
--更新已经被提醒过的记录
------------------------
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=1
AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-10,GETDATE()))
AND RemindTime>=DATEADD(Minute,-10,GETDATE()) 
AND RemindTime<=DATEADD(Minute,30,GETDATE())

--------------------------
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=2 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-30,GETDATE()))
AND RemindDay<>'' 
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)>=DATEADD(Minute,-10,GETDATE())
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)<=DATEADD(Minute,30,GETDATE())
--------------------------
SET DATEFIRST 1 --设定每周的第一天为星期一
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=3 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Minute,-30,GETDATE()))
AND charindex(','+ltrim(DATEPART(weekday,GETDATE()))+',',','+RemindWeekday+',')>0 -- 判断当前星期是否在RemindWeekday中
AND RemindDay<>'' 
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)>=DATEADD(Minute,-10,GETDATE())
AND CONVERT(DATETIME, CONVERT(VARCHAR(10),GETDATE(),120)+' '+RemindDay)<=DATEADD(Minute,30,GETDATE())
------------------------
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=4 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时
AND ( charindex(','+ltrim(DAY(GETDATE()))+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+1)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+2)+',',','+RemindMonth+',')>0
	OR charindex(','+ltrim(DAY(GETDATE())+3)+',',','+RemindMonth+',')>0
)
----------------------
UPDATE TimeRemind
SET LastRemindTime = GETDATE()
WHERE UserName=@UserName AND TimeType=5 AND (LastRemindTime IS NULL OR LastRemindTime<=DATEADD(Hour,-12,GETDATE())) --提醒的间隔时间为12小时
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)>=DATEADD(DAY,-1,GETDATE()) --时间范围 
AND CONVERT(DATETIME,Convert(varchar(4),year(GETDATE()))+'-'+RemindYear)<=DATEADD(DAY,3,GETDATE())

SET NOCOUNT ON;
END